/**
 * 
 */
package com.orange.modules.dao.expert;

import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.orange.dbhelp.DbHelper;
import com.orange.modules.entity.expert.AuthExportTaskMap;

/**
 * @author gaogs
 *
 * 2016-12-1
 */
@Repository
public class AuthExpertTaskMapRepository {
	
	@Resource(name = "dbHelper")
	private DbHelper dbHelper;
	
	@Autowired
	private AuthExpertTaskMapDao authExportTaskMapDao;
	
	/**
	 * 保存专家任务
	 * @param a
	 */
	public void saveAuthExportTaskMapInfo(AuthExportTaskMap a){
		authExportTaskMapDao.save(a);
	}
	
	/**
	 * 批量保存专家任务s
	 * @param a
	 */
	public void saveAuthExportTaskMapInfos(List<AuthExportTaskMap> as){
		authExportTaskMapDao.save(as);
	}
	
	/**
	 * 根据id删除专家任务
	 * @param pid
	 */
	public void deleteAuthExportTaskMapById(String pid){
		authExportTaskMapDao.delete(pid);
	}

	/**
	 * 删除某组内某专家的所有任务
	 * @param groupId
	 * @param exportId
	 * @throws SQLException 
	 */
	public void deleteAuthExportTaskByExportInfo(String groupId,String exportId) throws SQLException{
		String sql="DELETE FROM t_auth_export_task_map WHERE s_group_id =? AND s_export_id =?";
		dbHelper.execute(sql, groupId,exportId);
	}
	
	/**
	 * 查询某组内专家的所有任务
	 * @param groupId
	 * @param exportId
	 * @return
	 * @throws SQLException
	 */
	public List<AuthExportTaskMap> queryAuthExportTaskMaps(String groupId,String exportId)throws SQLException{
		String sql="SELECT a.p_id as pid ,a.s_export_id as exportId ,a.s_task_id  as taskId ,a.s_group_id as groupId ," +
				" a.s_type as type ,a.s_task_file_name as taskFileName," +
				" a.s_task_file_url as taskFileUrl  ,a.s_create_time as createTime" +
				" FROM t_auth_export_task_map as a WHERE a.s_group_id =? AND a.s_export_id =?";
		List<AuthExportTaskMap> ls=dbHelper.getBeanList(sql, AuthExportTaskMap.class, groupId,exportId);
		return ls;
	}
	
	/**
	 * 根据任务id查询组内成员
	 * @param groupId
	 * @param exportId
	 * @return
	 * @throws SQLException
	 */
	public List<AuthExportTaskMap> queryAuthExportTaskMapByGroupAndTaskId(String groupId,String taskId)throws SQLException{
		String sql="SELECT a.p_id as pid ,a.s_export_id as exportId ,a.s_task_id  as taskId ,a.s_group_id as groupId ," +
				" a.s_type as type ,a.s_task_file_name as taskFileName," +
				" a.s_task_file_url as taskFileUrl  ,a.s_create_time as createTime" +
				" FROM t_auth_export_task_map as a WHERE a.s_group_id =? AND a.s_task_id =?";
		List<AuthExportTaskMap> ls=dbHelper.getBeanList(sql, AuthExportTaskMap.class, groupId,taskId);
		return ls;
	}
	
	/**
	 * 保存专家 上传的测试项目用表
	 * @param a
	 */
	public void saveAuthExpertTaskWord(AuthExportTaskMap a){
		authExportTaskMapDao.save(a);
	}
	
	/**
	 * 根据专家id和任务id和组id 查询专家任务信息
	 * @param groupId
	 * @param taskId
	 * @param expertId
	 * @return
	 * @throws SQLException 
	 */
	public AuthExportTaskMap queryAuthExportTaskByGroupAndTaskAndExpertId(String groupId,String taskId,String expertId) throws SQLException{
		String sql="SELECT a.p_id as pid ,a.s_export_id as exportId ,a.s_task_id  as taskId ,a.s_group_id as groupId ," +
				" a.s_type as type ,a.s_task_file_name as taskFileName," +
				" a.s_task_file_url as taskFileUrl  ,a.s_create_time as createTime" +
				" FROM t_auth_export_task_map as a " +
				" WHERE a.s_group_id =? AND a.s_task_id =? AND a.s_export_id=?";
		AuthExportTaskMap l=dbHelper.getBean(sql, AuthExportTaskMap.class, groupId,taskId,expertId);
		return l;
	}
	
	/**
	 * 根据id查询专家任务
	 * @param id
	 * @return
	 */
	public AuthExportTaskMap queryAuthExportTaskMapById(String id){
		AuthExportTaskMap a=authExportTaskMapDao.findOne(id);
		return a;
	}
	
	
	/**
	 * 根据专家id和组id 查询专家任务信息
	 * @param groupId
	 * @param taskId
	 * @param expertId
	 * @return
	 * @throws SQLException 
	 */
	public AuthExportTaskMap queryAuthExportTaskByGroupAndExpertId(String groupId,String expertId) throws SQLException{
		String sql="SELECT TOP 1  a.p_id as pid ,a.s_export_id as exportId ,a.s_task_id  as taskId ,a.s_group_id as groupId ," +
				" a.s_type as type ,a.s_task_file_name as taskFileName," +
				" a.s_task_file_url as taskFileUrl  ,a.s_create_time as createTime" +
				" FROM t_auth_export_task_map as a " +
				" WHERE a.s_group_id =?  AND a.s_export_id=? AND a.s_task_file_url is not null ";
		AuthExportTaskMap l=dbHelper.getBean(sql, AuthExportTaskMap.class, groupId,expertId);
		return l;
	}
	
	
	
	
	
	
	
	
	
	
	
	
}
